ALTER TABLE ANSWERS 
DROP CONSTRAINT ANSWERS_QUESTIONS_FK1;

ALTER TABLE USER_ROLE_GRANTS 
DROP CONSTRAINT USER_ROLE_GRANTS_ROLES_FK1;

ALTER TABLE USER_ROLE_GRANTS 
DROP CONSTRAINT USER_ROLE_GRANTS_USERS_FK1;

ALTER TABLE USER_RESPONSES 
DROP CONSTRAINT USER_RESPONSES_USERS_FK1;

ALTER TABLE USER_EXAMS 
DROP CONSTRAINT USER_EXAMS_EXAMS_FK1;

ALTER TABLE USER_EXAMS 
DROP CONSTRAINT USER_EXAMS_USERS_FK1;

ALTER TABLE QUESTIONS 
DROP CONSTRAINT QUESTIONS_QUESTIONTYPE_FK1;

ALTER TABLE QUESTIONS 
DROP CONSTRAINT QUESTIONS_SUBJECTS_FK1;

ALTER TABLE QUESTIONS_IN_EXAM 
DROP CONSTRAINT QUESTIONS_IN_EXAM_EXAMS_FK1;

ALTER TABLE EXAMS 
DROP CONSTRAINT EXAMS_EXAM_TYPE_FK1;

ALTER TABLE EXAMS 
DROP CONSTRAINT EXAMS_REFSUBJECTS_FK1;

DROP TABLE PS_TXN CASCADE CONSTRAINTS;

DROP TABLE ANSWERS CASCADE CONSTRAINTS;

DROP TABLE USER_ROLE_GRANTS CASCADE CONSTRAINTS;

DROP TABLE USER_RESPONSES CASCADE CONSTRAINTS;

DROP TABLE USER_EXAMS CASCADE CONSTRAINTS;

DROP TABLE QUESTIONS CASCADE CONSTRAINTS;

DROP TABLE REF_SUBJECTS CASCADE CONSTRAINTS;

DROP TABLE QUESTION_REF_LINKS CASCADE CONSTRAINTS;

DROP TABLE EXAM_TYPE CASCADE CONSTRAINTS;

DROP TABLE QUESTIONS_IN_EXAM CASCADE CONSTRAINTS;

DROP TABLE EXAMS CASCADE CONSTRAINTS;

DROP TABLE REF_TYPE_OF_QUESTION CASCADE CONSTRAINTS;

DROP TABLE USERS CASCADE CONSTRAINTS;

DROP TABLE ROLES CASCADE CONSTRAINTS;

DROP SEQUENCE PS_TXN_SEQ;

DROP SEQUENCE ANSWERS_SEQ;

DROP SEQUENCE USERS_ROLES_GRANTS_SEQ;

DROP SEQUENCE QUESTIONS_SEQ;

DROP SEQUENCE EXAMS_SEQ;

DROP SEQUENCE QUESTIONSIN_EXAM_SEQ;

DROP SEQUENCE USERS_SEQ;

DROP SEQUENCE REFSUBJECTS_SEQ;

DROP SEQUENCE QUESTIONTYPE_SEQ;

DROP SEQUENCE EXAM_TYPE_SEQ;

CREATE TABLE PS_TXN 
(
  ID NUMBER(20, 0) NOT NULL 
, PARENTID NUMBER(20, 0) 
, COLLID NUMBER(10, 0) NOT NULL 
, CONTENT BLOB 
, CREATION_DATE DATE DEFAULT sysdate 
, CONSTRAINT PS_TXN_PK PRIMARY KEY 
  (
    COLLID 
  , ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
) 
LOB (CONTENT) STORE AS SYS_LOB0000019317C00004$$ 
( 
  ENABLE STORAGE IN ROW 
  CHUNK 8192 
  RETENTION 
  CACHE 
  LOGGING 
  STORAGE 
  ( 
    INITIAL 65536 
    MINEXTENTS 1 
    MAXEXTENTS 2147483645 
    BUFFER_POOL DEFAULT 
  )  
);

CREATE TABLE ANSWERS 
(
  ANSWER_ID NUMBER NOT NULL 
, QUESTION_ID NUMBER 
, ANSWER_TEXT VARCHAR2(500 BYTE) 
, VALID_ANSWER NUMBER(*, 0) 
, CONSTRAINT VALIDANSWERS_PK PRIMARY KEY 
  (
    ANSWER_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE USER_ROLE_GRANTS 
(
  ID NUMBER 
, USER_ID NUMBER 
, ROLE_ID NUMBER 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE USER_RESPONSES 
(
  USER_RESPONSE_ID NUMBER NOT NULL 
, EXAM_ID NUMBER 
, QUESTION_ID NUMBER 
, USER_ID NUMBER 
, DATE_OF_ANSWER DATE 
, COMMENTS VARCHAR2(50 BYTE) 
, USER_ANSWER_ID NUMBER NOT NULL 
, CONSTRAINT STUDENTANSWERS_PK PRIMARY KEY 
  (
    USER_RESPONSE_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE USER_EXAMS 
(
  USER_EXAM_ID NUMBER NOT NULL 
, USER_ID NUMBER 
, EXAM_ID NUMBER 
, CONSTRAINT USER_EXAMS_PK PRIMARY KEY 
  (
    USER_EXAM_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE QUESTIONS 
(
  QUESTION_ID NUMBER NOT NULL 
, QUESTION_REF_ID NUMBER 
, SUBJECT_ID NUMBER 
, IMAGESRC VARCHAR2(50 BYTE) 
, TYPE_OF_QUESTION_ID NUMBER(*, 0) NOT NULL 
, QUESTION_TEXT VARCHAR2(500 BYTE) 
, PRIORITY NUMBER(*, 0) 
, VALID_NO_OF_ANSWERS NUMBER(*, 0) 
, ATTRIBUTE1 VARCHAR2(20 BYTE) 
, QUESTION_REFERENCE VARCHAR2(2000 BYTE) 
, CONSTRAINT QUESTIONS_PK PRIMARY KEY 
  (
    QUESTION_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE REF_SUBJECTS 
(
  SUBJECT_ID NUMBER NOT NULL 
, SUBJECT_CODE VARCHAR2(20 BYTE) NOT NULL 
, SUBJECT_NAME VARCHAR2(20 BYTE) 
, SUBJECT_DESCRIPTION VARCHAR2(100 BYTE) 
, CONSTRAINT REF_SUBJECTS_PK1 PRIMARY KEY 
  (
    SUBJECT_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE QUESTION_REF_LINKS 
(
  QUESTION_REF_ID NUMBER(*, 0) NOT NULL 
, LINK VARCHAR2(50 BYTE) 
, CONSTRAINT QUESTION_REF_LINKS_PK PRIMARY KEY 
  (
    QUESTION_REF_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE EXAM_TYPE 
(
  EXAM_TYPE_ID NUMBER NOT NULL 
, EXAM_TYPE_NAME VARCHAR2(40 BYTE) NOT NULL 
, CONSTRAINT EXAM_TYPE_PK PRIMARY KEY 
  (
    EXAM_TYPE_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE QUESTIONS_IN_EXAM 
(
  Q_IN_EXAM_ID NUMBER(*, 0) NOT NULL 
, EXAM_ID NUMBER NOT NULL 
, QUESTION_ID NUMBER NOT NULL 
, PUBLISHED_DATE TIMESTAMP(6) 
, CONSTRAINT QUESTIONSINEXAM_PK PRIMARY KEY 
  (
    Q_IN_EXAM_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE EXAMS 
(
  EXAM_ID NUMBER NOT NULL 
, EXAM_TYPE_ID NUMBER 
, EXAM_CODE VARCHAR2(20 BYTE) 
, SUBJECT_ID NUMBER 
, EXAM_NAME VARCHAR2(50 BYTE) 
, EXAM_DESCRIPTION VARCHAR2(100 BYTE) 
, EXAM_DATE DATE 
, ATTRIBUTE1 VARCHAR2(50 BYTE) 
, CONSTRAINT EXAMS_PK PRIMARY KEY 
  (
    EXAM_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE REF_TYPE_OF_QUESTION 
(
  TYPE_OF_QUESTION_CODE VARCHAR2(20 BYTE) NOT NULL 
, TYPE_OF_QUESTION_ID NUMBER NOT NULL 
, TYPE_OF_QUESTION_DESCRIPTION VARCHAR2(50 BYTE) 
, CONSTRAINT REF_TYPE_OF_QUESTION_PK PRIMARY KEY 
  (
    TYPE_OF_QUESTION_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE USERS 
(
  LOGIN_ID VARCHAR2(100 BYTE) 
, USER_ID NUMBER NOT NULL 
, FIRST_NAME VARCHAR2(50 BYTE) 
, LAST_NAME VARCHAR2(50 BYTE) 
, ORG_KEY VARCHAR2(30 BYTE) DEFAULT 'DEFAULT' 
, MIDDLE_NAME VARCHAR2(50 BYTE) 
, GENDER_MF VARCHAR2(10 BYTE) 
, EMAIL_ADDRESS VARCHAR2(240 BYTE) 
, DISPLAY_NAME VARCHAR2(100 BYTE) 
, PASSWORD VARCHAR2(240 BYTE) 
, MOBILE VARCHAR2(20 BYTE) 
, OTHERDETAILS VARCHAR2(20 BYTE) 
, CONSTRAINT STUDENTS_PK PRIMARY KEY 
  (
    USER_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

CREATE TABLE ROLES 
(
  ROLE_ID NUMBER NOT NULL 
, ORG_KEY VARCHAR2(30 BYTE) 
, SHORT_NAME VARCHAR2(10 BYTE) 
, NAME VARCHAR2(40 BYTE) 
, CONSTRAINT ROLES_PK PRIMARY KEY 
  (
    ROLE_ID 
  )
  ENABLE 
) 
LOGGING 
TABLESPACE "USERS" 
PCTFREE 10 
INITRANS 1 
STORAGE 
( 
  INITIAL 65536 
  MINEXTENTS 1 
  MAXEXTENTS 2147483645 
  BUFFER_POOL DEFAULT 
);

ALTER TABLE ANSWERS
ADD CONSTRAINT ANSWERS_QUESTIONS_FK1 FOREIGN KEY
(
  QUESTION_ID 
)
REFERENCES QUESTIONS
(
  QUESTION_ID 
)
ENABLE;

ALTER TABLE USER_ROLE_GRANTS
ADD CONSTRAINT USER_ROLE_GRANTS_ROLES_FK1 FOREIGN KEY
(
  ROLE_ID 
)
REFERENCES ROLES
(
  ROLE_ID 
)
ENABLE;

ALTER TABLE USER_ROLE_GRANTS
ADD CONSTRAINT USER_ROLE_GRANTS_USERS_FK1 FOREIGN KEY
(
  USER_ID 
)
REFERENCES USERS
(
  USER_ID 
)
ENABLE;

ALTER TABLE USER_RESPONSES
ADD CONSTRAINT USER_RESPONSES_USERS_FK1 FOREIGN KEY
(
  USER_ID 
)
REFERENCES USERS
(
  USER_ID 
)
ENABLE;

ALTER TABLE USER_EXAMS
ADD CONSTRAINT USER_EXAMS_EXAMS_FK1 FOREIGN KEY
(
  EXAM_ID 
)
REFERENCES EXAMS
(
  EXAM_ID 
)
ENABLE;

ALTER TABLE USER_EXAMS
ADD CONSTRAINT USER_EXAMS_USERS_FK1 FOREIGN KEY
(
  USER_ID 
)
REFERENCES USERS
(
  USER_ID 
)
ENABLE;

ALTER TABLE QUESTIONS
ADD CONSTRAINT QUESTIONS_QUESTIONTYPE_FK1 FOREIGN KEY
(
  TYPE_OF_QUESTION_ID 
)
REFERENCES REF_TYPE_OF_QUESTION
(
  TYPE_OF_QUESTION_ID 
)
ENABLE;

ALTER TABLE QUESTIONS
ADD CONSTRAINT QUESTIONS_SUBJECTS_FK1 FOREIGN KEY
(
  SUBJECT_ID 
)
REFERENCES REF_SUBJECTS
(
  SUBJECT_ID 
)
ENABLE;

ALTER TABLE QUESTIONS_IN_EXAM
ADD CONSTRAINT QUESTIONS_IN_EXAM_EXAMS_FK1 FOREIGN KEY
(
  EXAM_ID 
)
REFERENCES EXAMS
(
  EXAM_ID 
)
ENABLE;

ALTER TABLE EXAMS
ADD CONSTRAINT EXAMS_EXAM_TYPE_FK1 FOREIGN KEY
(
  EXAM_TYPE_ID 
)
REFERENCES EXAM_TYPE
(
  EXAM_TYPE_ID 
)
ENABLE;

ALTER TABLE EXAMS
ADD CONSTRAINT EXAMS_REFSUBJECTS_FK1 FOREIGN KEY
(
  SUBJECT_ID 
)
REFERENCES REF_SUBJECTS
(
  SUBJECT_ID 
)
ENABLE;

ALTER TABLE USERS
ADD CONSTRAINT SYS_C005644 CHECK 
(FIRST_NAME IS NOT NULL)
DISABLE;

ALTER TABLE USERS
ADD CONSTRAINT SYS_C005645 CHECK 
(GENDER_MF IS NOT NULL)
DISABLE;

COMMENT ON COLUMN QUESTIONS.TYPE_OF_QUESTION_ID IS 'Foreign Key to Ref_Type_Of_Question Table';

COMMENT ON COLUMN REF_TYPE_OF_QUESTION.TYPE_OF_QUESTION_CODE IS 'This acts as primary key';

CREATE SEQUENCE PS_TXN_SEQ INCREMENT BY 50 MAXVALUE 999999999999999999999999999 MINVALUE 1 CACHE 20;

CREATE SEQUENCE ANSWERS_SEQ INCREMENT BY 1 MAXVALUE 99999999999 MINVALUE 1 NOCACHE;

CREATE SEQUENCE USERS_ROLES_GRANTS_SEQ INCREMENT BY 1 MAXVALUE 100000 MINVALUE 1 CYCLE NOCACHE;

CREATE SEQUENCE QUESTIONS_SEQ INCREMENT BY 1 MAXVALUE 9999999999 MINVALUE 1 NOCACHE;

CREATE SEQUENCE EXAMS_SEQ INCREMENT BY 1 MAXVALUE 10000 MINVALUE 1 NOCACHE;

CREATE SEQUENCE QUESTIONSIN_EXAM_SEQ INCREMENT BY 1 MAXVALUE 99999999 MINVALUE 1 NOCACHE;

CREATE SEQUENCE USERS_SEQ INCREMENT BY 1 MAXVALUE 99999 MINVALUE 1 NOCACHE;

CREATE SEQUENCE REFSUBJECTS_SEQ INCREMENT BY 1 MAXVALUE 100 MINVALUE 1 NOCACHE ORDER;

CREATE SEQUENCE QUESTIONTYPE_SEQ INCREMENT BY 1 MAXVALUE 10 MINVALUE 1 NOCACHE;

CREATE SEQUENCE EXAM_TYPE_SEQ INCREMENT BY 1 MAXVALUE 100 MINVALUE 1 NOCACHE;

CREATE OR REPLACE trigger TYPEOFQUESTION_SEQ_TGR before insert on "ONLINEQUIZ"."REF_TYPE_OF_QUESTION" 
   for each row 
begin  
   if inserting then 
      if :NEW."TYPE_OF_QUESTION_ID" is null then 
         select QUESTIONTYPE_SEQ.nextval into :NEW."TYPE_OF_QUESTION_ID" from dual; 
      end if; 
   end if; 
end;
/

CREATE OR REPLACE trigger QUESTIONS_SEQ_TRG before insert on "ONLINEQUIZ"."QUESTIONS" 
   for each row 
begin  
   if inserting then 
      if :NEW."QUESTION_ID" is null then 
         select QUESTIONS_SEQ.nextval into :NEW."QUESTION_ID" from dual; 
      end if; 
   end if; 
end;
/

CREATE OR REPLACE trigger EXAMS_SEQ_TRG before insert on "ONLINEQUIZ"."EXAMS" 
   for each row 
begin  
   if inserting then 
      if :NEW."EXAM_ID" is null then 
         select EXAMS_SEQ.nextval into :NEW."EXAM_ID" from dual; 
      end if; 
   end if; 
end;
/

CREATE OR REPLACE TRIGGER USERS_SEQ_TRG BEFORE INSERT ON USERS 
BEGIN
  NULL;
END;
/

CREATE OR REPLACE trigger ANSWERS_SEQ_TRG before insert on "ONLINEQUIZ"."ANSWERS" 
   for each row 
begin  
   if inserting then 
      if :NEW."ANSWER_ID" is null then 
         select ANSWERS_SEQ.nextval into :NEW."ANSWER_ID" from dual; 
      end if; 
   end if; 
end;
/

CREATE OR REPLACE trigger QUESTIONSINEXAM_SEQ_TRG before insert on "ONLINEQUIZ"."QUESTIONS_IN_EXAM" 
   for each row 
begin  
   if inserting then 
      if :NEW."Q_IN_EXAM_ID" is null then 
         select QUESTIONSIN_EXAM_SEQ.nextval into :NEW."Q_IN_EXAM_ID" from dual; 
      end if; 
   end if; 
end;
/

CREATE OR REPLACE trigger EXAMTYPE_SEQ_TRG before insert on "ONLINEQUIZ"."EXAM_TYPE" 
   for each row 
begin  
   if inserting then 
      if :NEW."EXAM_TYPE_ID" is null then 
         select EXAM_TYPE_SEQ.nextval into :NEW."EXAM_TYPE_ID" from dual; 
      end if; 
   end if; 
end;
/

CREATE OR REPLACE trigger REFSUBJECTS_TRG before insert on "ONLINEQUIZ"."REF_SUBJECTS" 
   for each row 
begin  
   if inserting then 
      if :NEW."SUBJECT_ID" is null then 
         select REFSUBJECTS_SEQ.nextval into :NEW."SUBJECT_ID" from dual; 
      end if; 
   end if; 
end;
/
